package com.dfbz.dao;

import java.util.List;
import java.util.Map;

public class HomeDao extends baseDao{
    public Map<String, Object> findCurData() {
        try {
            String sql = "SELECT\n" +
                    "    ( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) = CURDATE( ) ) userCount,\n" +
                    "    ( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = CURDATE( ) ) articleCount,\n" +
                    "    ( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = CURDATE( ) ) meetingCount";

            return jdbcTemplate.queryForMap(sql);
        } catch (Exception exception) {
            // 如果是没有查询到则返回null
            return null;
        }
    }

    public List<Map<String, Object>> findSevenData() {
        String sql = "SELECT\n" +
                "\t( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) =  ADDDATE(CURDATE(),-6) ) day6,\n" +
                "\t( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) =  ADDDATE(CURDATE(),-5) ) day5,\n" +
                "\t( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) =  ADDDATE(CURDATE(),-4) ) day4,\n" +
                "\t( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) =  ADDDATE(CURDATE(),-3) ) day3,\n" +
                "\t( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) =  ADDDATE(CURDATE(),-2) ) day2,\n" +
                "\t( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) =  ADDDATE(CURDATE(),-1) ) day1,\n" +
                "\t( SELECT count( 1 ) FROM USER WHERE DATE_FORMAT( register_time, \"%Y-%m-%d\" ) = \tCURDATE() ) today\n" +
                "union all\n" +
                "\n" +
                "SELECT\n" +
                "\t( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-6) ) day6,\n" +
                "\t( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-5) ) day5,\n" +
                "\t( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-4) ) day4,\n" +
                "\t( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-3) ) day3,\n" +
                "\t( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-2) ) day2,\n" +
                "\t( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-1) ) day1,\n" +
                "\t( SELECT count( 1 ) FROM article WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = CURDATE() ) today\n" +
                "union all\n" +
                "\n" +
                "SELECT\n" +
                "\t( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-6) ) day6,\n" +
                "\t( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-5) ) day5,\n" +
                "\t( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-4) ) day4,\n" +
                "\t( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-3) ) day3,\n" +
                "\t( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-2) ) day2,\n" +
                "\t( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = ADDDATE(CURDATE(),-1) ) day1,\n" +
                "\t( SELECT count( 1 ) FROM meeting WHERE DATE_FORMAT( publish_date, \"%Y-%m-%d\" ) = CURDATE() ) today";
        return jdbcTemplate.queryForList(sql);
    }
}
